/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * BalanceForm.java
 *
 * Created on Aug 5, 2010, 6:45:50 PM
 */

package stockcontrol;

import java.awt.EventQueue;
import java.awt.Frame;
import java.awt.Graphics;
import java.awt.PrintJob;
import java.awt.Toolkit;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import java.util.Properties;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.Query;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.data.category.DefaultCategoryDataset;

/**
 *
 * @author yue
 */
public class BalanceForm extends javax.swing.JPanel {

    String[] nombreMeses = {"Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre"};

    //CONSULTAS

    String findCantArtMes = "SELECT a.descrip, COUNT(*) FROM Articulo a, Venta v WHERE a.codigo = v.articulo AND MONTH(v.fecha) = :mes AND YEAR(v.fecha) = :anio GROUP BY a.descrip";
    String findCantArtAnio = "SELECT a.descrip, COUNT(*) FROM Articulo a, Venta v WHERE a.codigo = v.articulo AND YEAR(v.fecha) = :anio GROUP BY a.descrip";
    String findComisionVendedorMes = "SELECT h.nombre, SUM(v.comision) FROM Venta v, Vendedor h WHERE h.codigo = v.vendedor AND MONTH(v.fecha) = :mes AND YEAR(v.fecha) = :anio GROUP BY h.nombre";
    String findComisionVendedorAnio = "SELECT h.nombre, SUM(v.comision) FROM Venta v, Vendedor h WHERE h.codigo = v.vendedor AND YEAR(v.fecha) = :anio GROUP BY h.nombre";
    String findIngresosVendedorMes = "SELECT h.nombre, COUNT(*), SUM(v.importe) FROM Venta v, Vendedor h WHERE h.codigo = v.vendedor AND MONTH(v.fecha) = :mes AND YEAR(v.fecha) = :anio GROUP BY h.nombre";
    String findIngresosVendedorAnio = "SELECT h.nombre, COUNT(*), SUM(v.importe) FROM Venta v, Vendedor h WHERE h.codigo = v.vendedor AND YEAR(v.fecha) = :anio GROUP BY h.nombre";
    String findIngresosCatMes = "SELECT c.nombre, SUM(v.importe) FROM Articulo a, Venta v, Categoria c WHERE a.codigo = v.articulo AND a.codigo_cat = c.codigo AND MONTH(v.fecha) = :mes AND YEAR(v.fecha) = :anio GROUP BY c.nombre";
    String findIngresosCatAnio = "SELECT c.nombre, SUM(v.importe) FROM Articulo a, Venta v, Categoria c WHERE a.codigo = v.articulo AND a.codigo_cat = c.codigo AND YEAR(v.fecha) = :anio GROUP BY c.nombre";
    String findGastosCatMes = "SELECT t.nombre, SUM(c.importe) FROM Compra c, Categoria t WHERE t.codigo = c.codigo_cat AND MONTH(c.fecha) = :mes AND YEAR(c.fecha) = :anio GROUP BY t.nombre";
    String findGastosCatAnio = "SELECT t.nombre, SUM(c.importe) FROM Compra c, Categoria t WHERE t.codigo = c.codigo_cat AND YEAR(c.fecha) = :anio GROUP BY t.nombre";

    EntityManager em;

    /** Creates new form BalanceForm */
    public BalanceForm(EntityManager em) {
        this.em = em;
        initComponents();
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {
        bindingGroup = new org.jdesktop.beansbinding.BindingGroup();

        jButtonImprimir = new javax.swing.JButton();
        jButtonExportar = new javax.swing.JButton();
        jComboBoxMes = new javax.swing.JComboBox();
        jLabelMes = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        jTextFieldAnio = new javax.swing.JTextField();
        jButtonBalance = new javax.swing.JButton();
        jComboBoxPeriodo = new javax.swing.JComboBox();
        jButtonComisiones = new javax.swing.JButton();
        jButtonArtVend = new javax.swing.JButton();
        jSeparator1 = new javax.swing.JSeparator();
        jButtonIngVend = new javax.swing.JButton();
        jButtonGastoCat = new javax.swing.JButton();
        jButtonIngMes = new javax.swing.JButton();
        jButtonGastoMes = new javax.swing.JButton();
        jButtonIngCat = new javax.swing.JButton();
        jScrollPane2 = new javax.swing.JScrollPane();
        jTableResult = new javax.swing.JTable();
        jLabelTitulo = new javax.swing.JLabel();
        jButtonGrafico = new javax.swing.JButton();

        setName("Form"); // NOI18N

        org.jdesktop.application.ResourceMap resourceMap = org.jdesktop.application.Application.getInstance(stockcontrol.StockControlApp.class).getContext().getResourceMap(BalanceForm.class);
        jButtonImprimir.setText(resourceMap.getString("jButtonPrint.text")); // NOI18N
        jButtonImprimir.setName("jButtonPrint"); // NOI18N
        jButtonImprimir.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonImprimirActionPerformed(evt);
            }
        });

        jButtonExportar.setText(resourceMap.getString("jButtonExportar.text")); // NOI18N
        jButtonExportar.setName("jButtonExportar"); // NOI18N
        jButtonExportar.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonExportarActionPerformed(evt);
            }
        });

        jComboBoxMes.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Enero", "Febrero", "Marzo", "Abril", "Mayo", "Junio", "Julio", "Agosto", "Septiembre", "Octubre", "Noviembre", "Diciembre" }));
        jComboBoxMes.setName("jComboBoxMes"); // NOI18N

        org.jdesktop.beansbinding.Binding binding = org.jdesktop.beansbinding.Bindings.createAutoBinding(org.jdesktop.beansbinding.AutoBinding.UpdateStrategy.READ_WRITE, jComboBoxPeriodo, org.jdesktop.beansbinding.ELProperty.create("${selectedItem == \"Mensual\"}"), jComboBoxMes, org.jdesktop.beansbinding.BeanProperty.create("enabled"));
        bindingGroup.addBinding(binding);

        jLabelMes.setText(resourceMap.getString("jLabelMes.text")); // NOI18N
        jLabelMes.setName("jLabelMes"); // NOI18N

        jLabel2.setText(resourceMap.getString("jLabel2.text")); // NOI18N
        jLabel2.setName("jLabel2"); // NOI18N

        jTextFieldAnio.setColumns(4);
        jTextFieldAnio.setName("jTextFieldAnio"); // NOI18N

        jButtonBalance.setText(resourceMap.getString("jButtonGeneraBalance.text")); // NOI18N
        jButtonBalance.setName("jButtonGeneraBalance"); // NOI18N
        jButtonBalance.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonBalanceActionPerformed(evt);
            }
        });

        jComboBoxPeriodo.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Mensual", "Anual" }));
        jComboBoxPeriodo.setName("jComboBoxPeriodo"); // NOI18N

        jButtonComisiones.setText(resourceMap.getString("jButtonComisiones.text")); // NOI18N
        jButtonComisiones.setName("jButtonComisiones"); // NOI18N
        jButtonComisiones.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonComisionesActionPerformed(evt);
            }
        });

        jButtonArtVend.setText(resourceMap.getString("jButtonArtVend.text")); // NOI18N
        jButtonArtVend.setName("jButtonArtVend"); // NOI18N
        jButtonArtVend.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonArtVendActionPerformed(evt);
            }
        });

        jSeparator1.setName("jSeparator1"); // NOI18N

        jButtonIngVend.setText(resourceMap.getString("jButtonIngVend.text")); // NOI18N
        jButtonIngVend.setName("jButtonIngVend"); // NOI18N
        jButtonIngVend.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonIngVendActionPerformed(evt);
            }
        });

        jButtonGastoCat.setText(resourceMap.getString("jButtonGastoCat.text")); // NOI18N
        jButtonGastoCat.setName("jButtonGastoCat"); // NOI18N
        jButtonGastoCat.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonGastoCatActionPerformed(evt);
            }
        });

        jButtonIngMes.setText(resourceMap.getString("jButtonIngMes.text")); // NOI18N
        jButtonIngMes.setName("jButtonIngMes"); // NOI18N

        binding = org.jdesktop.beansbinding.Bindings.createAutoBinding(org.jdesktop.beansbinding.AutoBinding.UpdateStrategy.READ_WRITE, jComboBoxPeriodo, org.jdesktop.beansbinding.ELProperty.create("${selectedItem == \"Anual\"}"), jButtonIngMes, org.jdesktop.beansbinding.BeanProperty.create("enabled"));
        bindingGroup.addBinding(binding);

        jButtonIngMes.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonIngMesActionPerformed(evt);
            }
        });

        jButtonGastoMes.setText(resourceMap.getString("jButtonGastoMes.text")); // NOI18N
        jButtonGastoMes.setName("jButtonGastoMes"); // NOI18N

        binding = org.jdesktop.beansbinding.Bindings.createAutoBinding(org.jdesktop.beansbinding.AutoBinding.UpdateStrategy.READ_WRITE, jComboBoxPeriodo, org.jdesktop.beansbinding.ELProperty.create("${selectedItem == \"Anual\"}"), jButtonGastoMes, org.jdesktop.beansbinding.BeanProperty.create("enabled"));
        bindingGroup.addBinding(binding);

        jButtonGastoMes.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonGastoMesActionPerformed(evt);
            }
        });

        jButtonIngCat.setText(resourceMap.getString("jButtonIngCat.text")); // NOI18N
        jButtonIngCat.setName("jButtonIngCat"); // NOI18N
        jButtonIngCat.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonIngCatActionPerformed(evt);
            }
        });

        jScrollPane2.setName("jScrollPane2"); // NOI18N

        jTableResult.setName("jTableResult"); // NOI18N
        jScrollPane2.setViewportView(jTableResult);

        jLabelTitulo.setHorizontalAlignment(javax.swing.SwingConstants.CENTER);
        jLabelTitulo.setText(resourceMap.getString("jLabelTitulo.text")); // NOI18N
        jLabelTitulo.setBorder(javax.swing.BorderFactory.createEtchedBorder());
        jLabelTitulo.setName("jLabelTitulo"); // NOI18N

        jButtonGrafico.setText(resourceMap.getString("jButtonGrafico.text")); // NOI18N
        jButtonGrafico.setEnabled(false);
        jButtonGrafico.setName("jButtonGrafico"); // NOI18N
        jButtonGrafico.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonGraficoActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addGroup(javax.swing.GroupLayout.Alignment.LEADING, layout.createSequentialGroup()
                        .addComponent(jComboBoxPeriodo, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jLabel2)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 17, Short.MAX_VALUE)
                        .addComponent(jTextFieldAnio, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(6, 6, 6)
                        .addComponent(jLabelMes)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jComboBoxMes, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 200, Short.MAX_VALUE)
                        .addComponent(jButtonGrafico)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButtonExportar)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButtonImprimir))
                    .addComponent(jSeparator1, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, 770, Short.MAX_VALUE)
                    .addGroup(javax.swing.GroupLayout.Alignment.LEADING, layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false)
                            .addComponent(jButtonGastoMes, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jButtonIngMes, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jButtonGastoCat, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jButtonIngCat, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jButtonIngVend, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jButtonComisiones, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jButtonArtVend, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jButtonBalance, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, 174, Short.MAX_VALUE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                            .addComponent(jLabelTitulo, javax.swing.GroupLayout.DEFAULT_SIZE, 584, Short.MAX_VALUE)
                            .addComponent(jScrollPane2, javax.swing.GroupLayout.DEFAULT_SIZE, 584, Short.MAX_VALUE))))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jButtonImprimir)
                    .addComponent(jButtonExportar)
                    .addComponent(jComboBoxPeriodo, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel2)
                    .addComponent(jTextFieldAnio, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabelMes)
                    .addComponent(jComboBoxMes, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButtonGrafico))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jSeparator1, javax.swing.GroupLayout.PREFERRED_SIZE, 10, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jLabelTitulo, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addGroup(layout.createSequentialGroup()
                        .addGap(24, 24, 24)
                        .addComponent(jButtonBalance)
                        .addGap(18, 18, 18)
                        .addComponent(jButtonArtVend)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButtonComisiones)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButtonIngVend)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButtonIngCat)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButtonGastoCat)
                        .addGap(18, 18, 18)
                        .addComponent(jButtonIngMes)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButtonGastoMes))
                    .addComponent(jScrollPane2, 0, 0, Short.MAX_VALUE))
                .addContainerGap())
        );

        bindingGroup.bind();
    }// </editor-fold>//GEN-END:initComponents

    private void jButtonArtVendActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonArtVendActionPerformed
        String[] columnas = {"ARTICULO","CANTIDAD"};
        if (jComboBoxPeriodo.getSelectedItem().toString().equals("Mensual"))
            mostrarResultados(findCantArtMes, "ARTICULOS VENDIDOS EN ", columnas);
        else
            mostrarResultados(findCantArtAnio, "ARTICULOS VENDIDOS EN ", columnas);
        jButtonGrafico.setEnabled(false);
    }//GEN-LAST:event_jButtonArtVendActionPerformed

    private void jButtonIngMesActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonIngMesActionPerformed
        mostrarPorMes("Venta","Ingresos");
        jButtonGrafico.setEnabled(true);
    }//GEN-LAST:event_jButtonIngMesActionPerformed

    private void jButtonGastoMesActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonGastoMesActionPerformed
        mostrarPorMes("Compra","Gastos");
        jButtonGrafico.setEnabled(true);
    }//GEN-LAST:event_jButtonGastoMesActionPerformed

    private void jButtonIngVendActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonIngVendActionPerformed
        String[] columnas = {"VENDEDOR","CANTIDAD","INGRESO ($)"};
        if (jComboBoxPeriodo.getSelectedItem().toString().equals("Mensual"))
            mostrarResultados(findIngresosVendedorMes, "INGRESOS POR VENDEDOR EN ", columnas);
        else
            mostrarResultados(findIngresosVendedorAnio, "INGRESOS POR VENDEDOR EN ", columnas);
        jButtonGrafico.setEnabled(false);
    }//GEN-LAST:event_jButtonIngVendActionPerformed

    private void jButtonGastoCatActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonGastoCatActionPerformed
        String[] columnas = {"CATEGORÍA","GASTOS ($)"};
        if (jComboBoxPeriodo.getSelectedItem().toString().equals("Mensual"))
            mostrarResultados(findGastosCatMes, "GASTOS POR CATEGORÍA EN ", columnas);
        else
            mostrarResultados(findGastosCatAnio, "GASTOS POR CATEGORÍA EN ", columnas);
        jButtonGrafico.setEnabled(true);
    }//GEN-LAST:event_jButtonGastoCatActionPerformed

    private void jButtonIngCatActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonIngCatActionPerformed
        String[] columnas = {"CATEGORÍA","INGRESOS ($)"};
        if (jComboBoxPeriodo.getSelectedItem().toString().equals("Mensual"))
            mostrarResultados(findIngresosCatMes, "INGRESOS POR CATEGORÍA EN ", columnas);
        else
            mostrarResultados(findIngresosCatAnio, "INGRESOS POR CATEGORÍA EN ", columnas);
        jButtonGrafico.setEnabled(true);
    }//GEN-LAST:event_jButtonIngCatActionPerformed

    private void mostrarResultados(String consulta, String titulo, String[] columnas){
        
        if (jTextFieldAnio.getText().length()>0){
            String textoMes = "";

            Query query = em.createNativeQuery(consulta);

            query.setParameter("anio",Integer.parseInt(jTextFieldAnio.getText()));
            
            if (jComboBoxPeriodo.getSelectedItem().toString().equals("Mensual")){

                query.setParameter("mes", (jComboBoxMes.getSelectedIndex()+1));

                textoMes = "EL MES DE " + jComboBoxMes.getSelectedItem().toString().toUpperCase() + " DE ";
            }

            jLabelTitulo.setText(titulo + textoMes + jTextFieldAnio.getText());
            
            List l = query.getResultList();

            jTableResult.setModel(new javax.swing.table.DefaultTableModel(new Object[l.size()][columnas.length], columnas));
            for (int i=0; i<l.size(); i++){
                    Object[] tupla = (Object[])l.get(i);
                    for (int j=0; j<tupla.length; j++){
                        jTableResult.setValueAt(tupla[j], i, j);
                    }
            }

        }
    }
    
    private void mostrarPorMes(String tabla, String motivo){
        if (jTextFieldAnio.getText().length()>1){
            List l = em.createNamedQuery(tabla+".find"+motivo+"Mes")
                    .setParameter("anio",Integer.parseInt((jTextFieldAnio.getText()))).getResultList();
            jLabelTitulo.setText(motivo.toUpperCase() + " POR MES (AÑO " + jTextFieldAnio.getText() + ")\n\n");
            
            jTableResult.setModel(new javax.swing.table.DefaultTableModel(new Object[12][2], new String[] {"MES",motivo.toUpperCase() + " ($)"}));
            int j=0;
            for (int i=0; i<12; i++){
                String importe = "0.00";
                if (j<l.size()) {
                    Object[] tupla = (Object[])l.get(j);
                    if ((Integer)tupla[0] == i+1){
                        importe = tupla[1].toString();
                        j++;
                    }
                }
                jTableResult.setValueAt(nombreMeses[i], i, 0);
                jTableResult.setValueAt(importe, i, 1);
            }
        }
    }
    
    private void jButtonExportarActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonExportarActionPerformed
        JFileChooser f = new JFileChooser();
        f.setFileSelectionMode(f.SAVE_DIALOG);
        f.setDialogTitle("Exportar...");
        f.showSaveDialog(f);
        PrintWriter pw = null;
        try {
            String file_path=f.getSelectedFile().getAbsolutePath().concat(".txt");
            pw = new PrintWriter(new FileWriter(file_path));
            
            pw.print(jLabelTitulo.getText() + "\n\n");

            int filas = jTableResult.getRowCount();
            int columnas = jTableResult.getColumnCount();

            pw.print("(");
            for (int i = 0; i<columnas; i++){
                pw.print(jTableResult.getColumnName(i));
                if (i+1<columnas) pw.print(", ");
            }
            pw.print(")\n\n");

            for (int i = 0; i<filas; i++){
                for (int j = 0; j<columnas; j++){
                    if (jTableResult.getValueAt(i, j)!=null){
                            pw.print(jTableResult.getValueAt(i, j));
                            if (j+1<columnas) pw.print(", ");
                            else pw.print("\n");
                    }
                    else
                        pw.println();
                }
            }

        } catch (IOException ex) {}
        pw.close();

    }//GEN-LAST:event_jButtonExportarActionPerformed

    private void jButtonImprimirActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonImprimirActionPerformed
        Properties defaultProps = new Properties();
        PrintJob print=Toolkit.getDefaultToolkit().getPrintJob(new Frame(),"Imprimir",defaultProps);
        Graphics g = print.getGraphics();
        g.translate(30,(int)Math.round(print.getPageDimension().getHeight()*.03));
        if(g!=null)
        {
            g.drawString("SSD Taller Protegido Necochea", 20, 20);
            g.drawString(jLabelTitulo.getText(), 20, 40);
            for (int i=0; i<jTableResult.getColumnCount(); i++){
                if (i!=0)
                    g.drawString(jTableResult.getColumnName(i), 220+i*110, 80);
                else
                    g.drawString(jTableResult.getColumnName(i), 100, 80);
            }
            
            int contLinea= 80;
            int pagina = 1;

            for (int i=0; i<jTableResult.getRowCount(); i++){
                
                if ((contLinea + 140) > print.getPageDimension().getHeight()){
                        contLinea = 20;
                        g.drawString(""+pagina, (int)Math.round(print.getPageDimension().getWidth())/2, (int)Math.round(print.getPageDimension().getHeight())-70);
                        pagina++;
                        g=print.getGraphics();
                        g.translate(30,(int)Math.round(print.getPageDimension().getHeight()*.03));
                    }

                contLinea = contLinea+20;

                for (int j=0; j<jTableResult.getColumnCount(); j++){
                    if (j!=0)
                        g.drawString(jTableResult.getValueAt(i, j).toString(), 250+j*100, contLinea);
                    else
                        if (jTableResult.getValueAt(i, j)!=null)
                            g.drawString(jTableResult.getValueAt(i, j).toString(), 20, contLinea);
                }
            }

            g.drawString(""+pagina, (int)Math.round(print.getPageDimension().getWidth())/2, (int)Math.round(print.getPageDimension().getHeight())-70);
        
        }
        g.dispose();
        print.end();

    }//GEN-LAST:event_jButtonImprimirActionPerformed

    private void jButtonBalanceActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonBalanceActionPerformed

        jButtonGrafico.setEnabled(false);

        if (jTextFieldAnio.getText().length()>1){

            Query query;
            String periodo = "Anio";

            String titulo = "BALANCE DEL ";

            if (jComboBoxPeriodo.getSelectedItem().toString().equals("Mensual")){

                    periodo = "Mes";
                    query = em.createNativeQuery(findGastosCatMes).setParameter("mes", (jComboBoxMes.getSelectedIndex()+1));
                    titulo=titulo.concat("MES DE "+jComboBoxMes.getSelectedItem().toString().toUpperCase()+" DEL ");
            }
            else {
                    query = em.createNativeQuery(findGastosCatAnio);
            }

            titulo=titulo.concat("AÑO " + jTextFieldAnio.getText());

            jLabelTitulo.setText(titulo);

            query.setParameter("anio",Integer.parseInt(jTextFieldAnio.getText()));
            List l = query.getResultList();

            jTableResult.setModel(new javax.swing.table.DefaultTableModel(new Object[8+l.size()][1], new String[] {""}));

            double totalventas = getResultQuerySimple("Venta.findTotalVentas"+periodo,periodo);

            double totalgastos = getResultQuerySimple("Compra.findTotalGastos"+periodo,periodo);

            double totalcomisiones = getResultQuerySimple("Venta.findTotalComisiones"+periodo,periodo);

            jTableResult.setValueAt("TOTAL VENTAS: $" + String.format("%.2f",totalventas), 0, 0);

            jTableResult.setValueAt("TOTAL GASTOS: $" + String.format("%.2f",totalgastos), 2, 0);
            jTableResult.setValueAt("TOTAL COMISIONES PAGADAS: $" + String.format("%.2f",totalcomisiones), 3, 0);

            jTableResult.setValueAt("TOTAL EFECTIVO EXISTENTE: $" + String.format("%.2f",totalventas+totalcomisiones-totalgastos), 5, 0);

            jTableResult.setValueAt("GASTOS POR CATEGORÍAS: ", 7, 0);

            for (int i=0; i<l.size(); i++){
                        Object[] tupla = (Object[])l.get(i);
                        String valor = tupla[0].toString().replace("    ", "") + ":\t $" + tupla[1];
                        jTableResult.setValueAt(valor, 8+i, 0);
            }
        }

    }//GEN-LAST:event_jButtonBalanceActionPerformed

    private void jButtonComisionesActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonComisionesActionPerformed
        String[] columnas = {"VENDEDOR","COMISIONES ($)"};
        if (jComboBoxPeriodo.getSelectedItem().toString().equals("Mensual"))
            mostrarResultados(findComisionVendedorMes, "COMISIONES POR VENDEDOR EN ", columnas);
        else
            mostrarResultados(findComisionVendedorAnio, "COMISIONES POR VENDEDOR EN ", columnas);
    }//GEN-LAST:event_jButtonComisionesActionPerformed

    private void jButtonGraficoActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonGraficoActionPerformed
        // Creamos y rellenamos el modelo de datos
        DefaultCategoryDataset dataset = new DefaultCategoryDataset();

        // Cargo los datos
        for (int i = 0; i < jTableResult.getRowCount(); i++) {
            dataset.setValue(Double.parseDouble(jTableResult.getValueAt(i, 1).toString()), jTableResult.getValueAt(i, 0).toString(), jTableResult.getValueAt(i, 0).toString());
        }

        JFreeChart chart = ChartFactory.createBarChart3D(jLabelTitulo.getText(), jTableResult.getColumnName(0), jTableResult.getColumnName(1), dataset, PlotOrientation.VERTICAL, true, true, false);
        JFrame ventana = new JFrame("Gráfico de " + jLabelTitulo.getText().toLowerCase());
        ventana.getContentPane().add(new ChartPanel(chart));
        ventana.pack();
        ventana.setVisible(true);
    }//GEN-LAST:event_jButtonGraficoActionPerformed

    public double getResultQuerySimple(String consulta, String periodo){
        Query q = em.createNamedQuery(consulta)
                    .setParameter("anio",Integer.parseInt((jTextFieldAnio.getText())));
        if (periodo.equals("Mes"))
            q.setParameter("mes", (jComboBoxMes.getSelectedIndex()+1));
        try {
            return Float.parseFloat(q.getSingleResult().toString());
        }
        catch (NoResultException e){
            return 0;
        }
        
    }

    public static void main(String[] args) {
        EventQueue.invokeLater(new Runnable() {
            public void run() {
                JFrame frame = new JFrame();
                frame.setContentPane(new BalanceForm(null));
                frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
                frame.pack();
                frame.setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton jButtonArtVend;
    private javax.swing.JButton jButtonBalance;
    private javax.swing.JButton jButtonComisiones;
    private javax.swing.JButton jButtonExportar;
    private javax.swing.JButton jButtonGastoCat;
    private javax.swing.JButton jButtonGastoMes;
    private javax.swing.JButton jButtonGrafico;
    private javax.swing.JButton jButtonImprimir;
    private javax.swing.JButton jButtonIngCat;
    private javax.swing.JButton jButtonIngMes;
    private javax.swing.JButton jButtonIngVend;
    private javax.swing.JComboBox jComboBoxMes;
    private javax.swing.JComboBox jComboBoxPeriodo;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabelMes;
    private javax.swing.JLabel jLabelTitulo;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JSeparator jSeparator1;
    private javax.swing.JTable jTableResult;
    private javax.swing.JTextField jTextFieldAnio;
    private org.jdesktop.beansbinding.BindingGroup bindingGroup;
    // End of variables declaration//GEN-END:variables

}
